package com.example.chcrm.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.chcrm.entity.StatsDTO.ConsignorStatsDTO;
import com.example.chcrm.entity.StatsDTO.SupplierStatsDTO;
import com.example.chcrm.entity.StatsDTO.VehicleStatistics;
import com.example.chcrm.entity.common.*;
import com.example.chcrm.entity.consignor.CustomerCarBrand;
import com.example.chcrm.entity.consignor.CustomerCarModel;
import com.example.chcrm.entity.consignor.CustomerInfo;
import com.example.chcrm.entity.employee.EmployeeVo;
import com.example.chcrm.entity.employee.SupplierPort;
import com.example.chcrm.entity.vehicle.ActualTransportation;
import com.example.chcrm.entity.vehicle.TransportChildCv;
import com.example.chcrm.entity.vehicle.TransportPlanCv;
import com.example.chcrm.entity.vehicle.VehicleDeployCarCv;
import com.example.chcrm.mapper.*;
import com.example.chcrm.service.TransportChildCvService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @Author 邹
 * @Date 2025/4/24 11:18
 * @Version 1.0
 */
@Service
public class TransportChildCvServiceImpl<T> implements TransportChildCvService<T> {
    @Autowired
    TransportChildCvMapper transportChildCvMapper;
    @Autowired
    TransportPlanCvMapper transportPlanCvMapper;
    @Autowired
    CustomerCarBrandMapper customerCarBrandMapper;
    @Autowired
    CustomerCarModelMapper customerCarModelMapper;
    @Autowired
    LineConfigMapper lineConfigMapper;
    @Autowired
    TAreaMapper tAreaMapper;
    @Autowired
    SupplierPortMapper supplierPortMapper;
    @Autowired
    OperateRecordMapper operateRecordMapper;
    @Autowired
    CustomerInfoMapper customerInfoMapper;
    @Autowired
    SupplierPortMapper supplierPortOtherMapper;
    @Autowired
    VehicleDeployCarCvMapper vehicleDeployCarCvMapper;
    @Override
    public ResponseMsg<IPage<TransportChildCv>> pageList(TransportChildCv transportChild) {
        int type1 = 0;
        int type2 = 0;
        int type3 = 0;
        if (StringUtils.isNotBlank(transportChild.getBatchNumber())) {
            String[] batchNumberLis = transportChild.getBatchNumber().split(",");
            if (batchNumberLis.length > 1) {
                type1 = 1;
                String batchNumber = transportChild.getBatchNumber().replace(",", "','");
                transportChild.setBatchNumber("'" + batchNumber + "'");
            }
        }
        if (StringUtils.isNotBlank(transportChild.getWaybillNo())) {
            String[] waybillNoLis = transportChild.getWaybillNo().split(",");
            if (waybillNoLis.length > 1) {
                type2 = 1;
                String waybillNo = transportChild.getWaybillNo().replace(",", "','");
                transportChild.setWaybillNo("'" + waybillNo + "'");
            }
        }
        if (StringUtils.isNotBlank(transportChild.getFrameNumber())) {
            String[] frameNumberLis = transportChild.getFrameNumber().split(",");
            if (frameNumberLis.length > 1) {
                type3 = 1;
                String frameNumber = transportChild.getFrameNumber().replace(",", "','");
                transportChild.setFrameNumber("'" + frameNumber + "'");
            }
        }
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//定义新的日期格式
        if (transportChild.getStartTime()!=null){
            Date currentTime = transportChild.getStartTime();
            String dateString = formatter.format(currentTime);
            try {
                Date date = formatter.parse(dateString);//parse():String--->date
                transportChild.setStartTime(date);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        if (transportChild.getEndTime()!=null){
            Date currentTime2 = transportChild.getEndTime();
            String dateString2 = formatter.format(currentTime2);
            try {
                Date date2 = formatter.parse(dateString2);//parse():String--->date
                transportChild.setEndTime(date2);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        List<TransportChildCv> list = transportChildCvMapper.selectLists(transportChild, type1, type2, type3);
        ResponseMsg<IPage<TransportChildCv>> msg = new ResponseMsg<>();
        Page<TransportChildCv> page = new Page<>();
        page.setRecords(list);
        page.setTotal(list.size());
        page.setSize(transportChild.getRows());
        page.setCurrent(transportChild.getPage());
        msg.setData(page);
        msg.success("查询成功");
        return msg;
    }

    @Override
    public ResponseMsg<Object> add(TransportChildCv transportChild, EmployeeVo empVo) {
        ResponseMsg<Object> msg = new ResponseMsg<>();
        if (!StringUtils.isNotBlank(transportChild.getWaybillNo())) {
            msg.fail("操作失败，运单号不能为空");
            return msg;
        }
        if (!StringUtils.isNotBlank(transportChild.getFrameNumber())) {
            msg.fail("操作失败，车架号不能为空");
            return msg;
        }
        if (!StringUtils.isNotBlank(transportChild.getModelName())) {
            msg.fail("操作失败，型号不能为空");
            return msg;
        }
        if (transportChild.getPlanDate() == null) {
            msg.fail("操作失败，计划时间不能为空");
            return msg;
        }
        //验证车架号是否存在
        int countFN = transportChildCvMapper.selectCount(new QueryWrapper<TransportChildCv>()
                .eq("frame_number", transportChild.getFrameNumber())
                .ne("state", 3)
                .eq("is_deleted", BasicDoc.NOT_DELETE)).intValue();
        if (countFN > 0) {
            msg.fail("操作失败，车架号已存在");
            return msg;
        }
        //验证型号是否存在
        CustomerCarModel customerCarModel = customerCarModelMapper.selectOne(new QueryWrapper<CustomerCarModel>()
                .eq("brand_name",transportChild.getBrandName())
                .eq("model", transportChild.getModelName())
                .eq("is_deleted", BasicDoc.NOT_DELETE));
        if (customerCarModel == null || customerCarModel.getIsDeleted() != BasicDoc.NOT_DELETE) {
            msg.fail("操作失败，型号未找到");
            return msg;
        }

        //查询运输计划
         List<TransportPlanCv>  transportPlans = transportPlanCvMapper.selectList(null,new QueryWrapper<TransportPlanCv>()
                .eq("batch_number",transportChild.getBatchNumber()).eq("is_deleted",BasicDoc.NOT_DELETE));
        if (transportPlans.size()==0) {
            msg.fail("操作失败，运输计划未找到");
            return msg;
        }
        if (transportPlans.size()>1) {
            msg.fail("操作失败，运输计划有误");
            return msg;
        }
        transportChild.setBasicId(transportPlans.get(0).getId());
        TransportPlanCv transportPlan = transportPlans.get(0);
        LineConfig lineConfig1 = null;
        LineConfig lineConfig2 = null;
        LineConfig lineConfig3 = null;
        //-----------验证线路1-----------
        if (transportPlan.getTransportMode() == 3 && transportChild.getDeparturePlaceType() ==1) {
            if (!StringUtils.isNotBlank(transportChild.getDeparturePlace())) {
                msg.fail("操作失败，起运地不能为空");
                return msg;
            }
            if (!StringUtils.isNotBlank(transportChild.getDeparturePort())) {
                msg.fail("操作失败，起运港不能为空");
                return msg;
            }
            TArea departurePlace = tAreaMapper.selectOne(new QueryWrapper<TArea>().eq("parent_name_lis",transportChild.getDeparturePlace()));
            if (departurePlace==null) {
                msg.fail("操作失败，起运地未找到");
                return msg;
            }
            SupplierPort departurePort = supplierPortMapper.selectOne(new QueryWrapper<SupplierPort>().eq("port_name",transportChild.getDeparturePort()));
            if (departurePort==null) {
                msg.fail("操作失败，起运港未找到");
                return msg;
            }
            //验证线路是否存在
            lineConfig1 = lineConfigMapper.selectOne(new QueryWrapper<LineConfig>()
                    .eq("line_type", 1)
                    .eq("start_address_id", departurePlace.getAdCode())
                    .isNull("departure_port_id")
                    .eq("departure_port_id", departurePort.getId())
                    .eq("is_deleted", BasicDoc.NOT_DELETE));
            if (lineConfig1 == null) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                msg.fail("添加失败，起运地到起运港线路不存在");
                return msg;
            }
            transportChild.setLine1Id(lineConfig1.getId());
            transportChild.setDeparturePlaceType(1);
            transportChild.setDeparturePlaceFullId(lineConfig1.getStartFullAddressId());
            transportChild.setDeparturePlace(lineConfig1.getStartAddressName());
            transportChild.setDeparturePlaceId(departurePlace.getAdCode());
        }
        //-----------验证线路2-----------
        if (transportPlan.getTransportMode() == 2 || transportPlan.getTransportMode() == 3) {
            if (!StringUtils.isNotBlank(transportChild.getDeparturePort())) {
                msg.fail("操作失败，起运港不能为空");
                return msg;
            }
            SupplierPort departurePort = supplierPortMapper.selectOne(new QueryWrapper<SupplierPort>().eq("port_name",transportChild.getDeparturePort()));
            if (departurePort==null) {
                msg.fail("操作失败，起运港未找到");
                return msg;
            }
            if (!StringUtils.isNotBlank(transportChild.getArrivePort())) {
                msg.fail("操作失败，目的港不能为空");
                return msg;
            }
            SupplierPort arrivePort = supplierPortMapper.selectOne(new QueryWrapper<SupplierPort>().eq("port_name",transportChild.getArrivePort()));
            if (arrivePort==null) {
                msg.fail("操作失败，目的港未找到");
                return msg;
            }
            lineConfig2 = lineConfigMapper.selectOne(new QueryWrapper<LineConfig>()
                    .eq("line_type", 2)
                    .eq("departure_port_id", departurePort.getId())
                    .eq("arrive_port_id", arrivePort.getId())
                    .eq("is_deleted", BasicDoc.NOT_DELETE));
            if (lineConfig2 == null) {
                msg.fail("添加失败，起运港到目的港线路不存在");
                return msg;
            }
            //赋值起始区域位置
//            if (transRulesType[0].equals("GK")) {
//                transportChild.setDeparturePlaceType(2);
                /*transportChild.setDeparturePlaceId(lineConfig2.getStartAddressId());
                transportChild.setDeparturePlace(lineConfig2.getStartAddressName());*/
//            }
            //赋值起始港位置
            transportChild.setDeparturePort(lineConfig2.getDeparturePort());
            transportChild.setDeparturePortId(lineConfig2.getDeparturePortId());
            transportChild.setDeparturePortAreaId(lineConfig2.getStartAddressId());
            transportChild.setDeparturePortArea(lineConfig2.getStartAddressName());
            //赋值交车港位置
            transportChild.setArrivePort(lineConfig2.getArrivePort());
            transportChild.setArrivePortId(lineConfig2.getArrivePortId());
            transportChild.setArrivePortAreaId(lineConfig2.getEndAddressId());
            transportChild.setArrivePortArea(lineConfig2.getEndAddressName());
            //赋值到达区域位置
//            if (transRulesType[1].equals("GK")) {
//                transportChild.setArrivePlaceType(2);
                /*transportChild.setArrivePlaceId(lineConfig2.getEndAddressId());
                transportChild.setArrivePlace(lineConfig2.getEndAddressName());*/
//            }
            transportChild.setLine2Id(lineConfig2.getId());
        }
        //-----------验证线路3-----------
        if (transportPlan.getTransportMode() == 1 || (transportPlan.getTransportMode() == 3 && transportChild.getArrivePlaceType()!=2)) {
            QueryWrapper<LineConfig> query = new QueryWrapper<LineConfig>();
            String startAdd = "";
            String endAdd = "";
            if (transportPlan.getTransportMode() == 1) {
                //验证起始位置
                if (transportChild.getDeparturePlaceType()==2) {
                    if (!StringUtils.isNotBlank(transportChild.getDeparturePort())) {
                        msg.fail("添加失败，起运港不能为空");
                        return msg;
                    }
                    SupplierPort departurePort = supplierPortMapper.selectOne(new QueryWrapper<SupplierPort>().eq("port_name",transportChild.getDeparturePort()));
                    if (departurePort==null) {
                        msg.fail("操作失败，起运港未找到");
                        return msg;
                    }
                    query.eq("departure_port_id", departurePort.getId());
                    transportChild.setDeparturePlaceType(2);
                    startAdd = "起始港";
                }else {
                    if (!StringUtils.isNotBlank(transportChild.getDeparturePlace())) {
                        msg.fail("添加失败，起运地不能为空");
                        return msg;
                    }
                    TArea departurePlace = tAreaMapper.selectOne(new QueryWrapper<TArea>().eq("parent_name_lis",transportChild.getDeparturePlace()));
                    if (departurePlace==null) {
                        msg.fail("操作失败，起运地未找到");
                        return msg;
                    }
                    query.eq("start_address_id", departurePlace.getAdCode());
                    query.isNull("departure_port_id");
                    transportChild.setDeparturePlaceType(1);
                    startAdd = "起运地";
                }
                //验证交车位置
                if (transportChild.getArrivePlaceType()==2) {
                    if (!StringUtils.isNotBlank(transportChild.getArrivePort())) {
                        msg.fail("添加失败，目的港不能为空");
                        return msg;
                    }
                    SupplierPort arrivePort = supplierPortMapper.selectOne(new QueryWrapper<SupplierPort>().eq("port_name",transportChild.getArrivePort()));
                    if (arrivePort==null) {
                        msg.fail("操作失败，目的港未找到");
                        return msg;
                    }
                    query.eq("arrive_port_id", arrivePort.getId());
                    transportChild.setArrivePlaceType(2);
                    endAdd = "交车港";
                } else {
                    if (!StringUtils.isNotBlank(transportChild.getArrivePlace())) {
                        msg.fail("添加失败，交车地不能为空");
                        return msg;
                    }
                    TArea arrivePlace = tAreaMapper.selectOne(new QueryWrapper<TArea>().eq("parent_name_lis",transportChild.getArrivePlace()));
                    if (arrivePlace==null) {
                        msg.fail("操作失败，起运地未找到");
                        return msg;
                    }
                    query.eq("end_address_id", arrivePlace.getAdCode());
                    query.isNull("arrive_port_id");
                    transportChild.setArrivePlaceType(1);
                    endAdd = "交车地";
                }
            } else {
                if (!StringUtils.isNotBlank(transportChild.getArrivePlace())) {
                    msg.fail("添加失败，交车地不能为空");
                    return msg;
                }
                TArea arrivePlace = tAreaMapper.selectOne(new QueryWrapper<TArea>().eq("parent_name_lis",transportChild.getArrivePlace()));
                if (arrivePlace==null) {
                    msg.fail("操作失败，起运地未找到");
                    return msg;
                }
                SupplierPort arrivePort = supplierPortMapper.selectOne(new QueryWrapper<SupplierPort>().eq("port_name",transportChild.getArrivePort()));
                if (arrivePort==null) {
                    msg.fail("操作失败，目的港未找到");
                    return msg;
                }
                query.eq("departure_port_id", arrivePort.getId());
                query.eq("end_address_id", arrivePlace.getAdCode());
                query.isNull("arrive_port_id");
                transportChild.setArrivePlaceType(1);
                startAdd = "交车港";
                endAdd = "交车地";
            }
            query.eq("line_type", 1);
            query.eq("is_deleted", BasicDoc.NOT_DELETE);
            lineConfig3 = lineConfigMapper.selectOne(query);
            if (lineConfig3 == null) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                msg.fail("添加失败，" + startAdd + "到" + endAdd + "线路未找到");
                return msg;
            }
            if (transportPlan.getTransportMode() == 1) {
                if (transportChild.getDeparturePlaceType()==2) {
                    transportChild.setDeparturePort(lineConfig3.getDeparturePort());
                    transportChild.setDeparturePortAreaId(lineConfig3.getStartAddressId());
                    transportChild.setDeparturePortArea(lineConfig3.getStartAddressName());
                } else {
                    transportChild.setDeparturePlaceId(lineConfig3.getStartAddressId());
                    transportChild.setDeparturePlaceFullId(lineConfig3.getStartFullAddressId());
                    transportChild.setDeparturePlace(lineConfig3.getStartAddressName());
                }
            }
            if (transportChild.getArrivePlaceType()==2) {
                transportChild.setArrivePort(lineConfig3.getArrivePort());
                transportChild.setArrivePortAreaId(lineConfig3.getEndAddressId());
                transportChild.setArrivePortArea(lineConfig3.getEndAddressName());
            } else {
                transportChild.setArrivePlaceId(lineConfig3.getEndAddressId());
                transportChild.setArrivePlaceFullId(lineConfig3.getEndFullAddressId());
                transportChild.setArrivePlace(lineConfig3.getEndAddressName());
            }
            transportChild.setLine3Id(lineConfig3.getId());
        }
        transportChild.setId(RandomHelper.uuid());
        transportChild.setConsignorId(transportPlan.getConsignorId());      //委托商id
        transportChild.setConsignorName(transportPlan.getConsignorName());  //委托商名称
        transportChild.setBatchNumber(transportPlan.getBatchNumber());      //批次号
        transportChild.setBrandId(customerCarModel.getBrandId());       //品牌id
        transportChild.setBrandName(customerCarModel.getBrandName());   //品牌名称
        transportChild.setModelName(customerCarModel.getModel());       //型号名称
        transportChild.setModelId(customerCarModel.getId());
        transportChild.setConfig(customerCarModel.getConfig());         //配置
        transportChild.setWeight(customerCarModel.getWeight());         //重量
        transportChild.setLwh(customerCarModel.getLength() + "x" + customerCarModel.getWidth() + "x" + customerCarModel.getHeight());
        //赋值当前位置
        if (transportChild.getLine1Id() != null) {
            transportChild.setActualPositionId(lineConfig1.getStartAddressId());
            transportChild.setActualPositionFullId(lineConfig1.getStartFullAddressId());
            transportChild.setActualPositionName(lineConfig1.getStartAddressName());
            transportChild.setActualPositionCoordinate(lineConfig1.getStartingCoordinate());
        } else if (lineConfig1 == null && lineConfig2 != null) {
            transportChild.setActualPositionId(lineConfig2.getStartAddressId());
            transportChild.setActualPositionFullId(lineConfig2.getStartFullAddressId());
            transportChild.setActualPositionName(lineConfig2.getStartAddressName());
            transportChild.setActualPositionCoordinate(lineConfig2.getStartingCoordinate());
            transportChild.setActualPortId(lineConfig2.getDeparturePortId());
            transportChild.setActualPortName(lineConfig2.getDeparturePort());
        } else {
            transportChild.setActualPositionId(lineConfig3.getStartAddressId());
            transportChild.setActualPositionFullId(lineConfig3.getStartFullAddressId());
            transportChild.setActualPositionName(lineConfig3.getStartAddressName());
            transportChild.setActualPositionCoordinate(lineConfig3.getStartingCoordinate());
            transportChild.setActualPortId(lineConfig3.getDeparturePortId());
            transportChild.setActualPortName(lineConfig3.getDeparturePort());
        }
        transportChild.setIsOutStorage(0);
        transportChild.setState(0);
        transportChild.setIsDeleted(BasicDoc.NOT_DELETE);
        transportChild.setCreateTime(new Date());
        transportChildCvMapper.insert(transportChild);
        msg.success("操作成功");
        return msg;
    }

    @Override
    public ResponseMsg<Object> detail(String id) {
        ResponseMsg<Object> msg = new ResponseMsg<>();
        TransportChildCv transportChild = transportChildCvMapper.selectById(id);
        if (transportChild == null || transportChild.getIsDeleted() != BasicDoc.NOT_DELETE) {
            msg.fail("查询失败，数据未找到");
            return msg;
        }
        TransportPlanCv transportPlan = transportPlanCvMapper.selectById(transportChild.getBasicId());
        if (transportPlan == null || transportPlan.getIsDeleted() != BasicDoc.NOT_DELETE) {
            msg.fail("查询失败，数据未找到");
            return msg;
        }
        transportPlan.setTransportChild(transportChild);
        msg.setData(transportPlan);
        msg.success("查询成功");
        return msg;
    }

    @Override
    @Transactional(rollbackFor = RuntimeException.class)
    public ResponseMsg<Object> importExcelxlAddOlan(Workbook workbook, EmployeeVo empVo) {
        ResponseMsg<Object> msg = new ResponseMsg<>();
//        boolean number = true;//判断资源车的查询次数
//        int resourcesNumber = 1000000;//记录资源车的号数
        List<String> planIdList = new ArrayList<>();//记录计划id
        //-------------------验证excelxl格式-------------------
        int sheetNum = workbook.getNumberOfSheets(); //获取sheet数量
        if (sheetNum < 1) {
            msg.fail("操作失败，excel格式不正确");
            return msg;
        }
        Sheet sheetOne = workbook.getSheetAt(0);    //获得第一个sheet
        int totalRows = sheetOne.getPhysicalNumberOfRows();      //sheet1行数
        int totalCells = 0;     //sheet1列数
        if (totalRows < 2) {
            msg.fail("操作失败，至少导入1条数据");
            return msg;
        }
       /* if (totalRows > 1100) {
            msg.fail("操作失败，最多导入1100条数据");
            return msg;
        }*/
        // 得到sheet1的列数(前提是有行数)
        if (totalRows > 1 && sheetOne.getRow(0) != null) {
            totalCells = sheetOne.getRow(0).getPhysicalNumberOfCells();
        }
        if (totalCells < 18) {
            msg.fail("操作失败，数据格式不正确");
            return msg;
        }
        ConcurrentMap<String,TransportChildCv> repeatMap=new ConcurrentHashMap<>();//验证是否重复
        //-------------------开始组装计划表数据-------------------
        Map<String, CustomerInfo> customerMap = new HashMap<>();    //记录委托商
        Map<String, CustomerCarBrand> brandMap = new HashMap<>();   //记录品牌
        Map<String, CustomerCarModel> modelMap = new HashMap<>();   //记录型号
        Map<String, SupplierPort> portMap = new HashMap<>();   //港口及其它
        Map<String, TransportPlanCv> transportPlanMap = new HashMap<>();     //记录运输计划
        List<TransportChildCv> transportChildCvList = new ArrayList<>();//存储ChildCv表数据
        Date date=new Date();
        List<String> perfectLiFramenos = new ArrayList<>();
//        Map<String, String> depaturePortFramenos = new HashMap<>();    //记录计划类型
        int countNum = 0;
        a:
        for (int r = 1; r < totalRows; r++) {
            Row row = sheetOne.getRow(r);
            if (row == null) {
                continue;
            }
            TransportPlanCv transportPlanCv = new TransportPlanCv();        //运输计划
            TransportChildCv transportChildCv = new TransportChildCv();     //计划委托车
            boolean isAddPlan = false;       //是否新增计划，默认否
            for (int i = 0; i < totalCells; i++) {
                Cell cell = row.getCell(i);
                String cellValue = null;
                if (i != 18) {
                    try {
                        cellValue = getCellValue(cell);
                    } catch (Exception e) {
                        msg.fail("解析第" + (r + 1) + "行" + (i + 1) + "列出现问题");
                    }
                }
                switch (i) {
                    case 0: //批次号
                        if (StringUtils.isEmpty(cellValue)) {
                            break a;
                        }
                        transportPlanCv = transportPlanMap.get(cellValue);
                        if (transportPlanCv == null) {
                            //需要验证当前批次号是否重复
                            transportPlanCv = transportPlanCvMapper.selectOne(new QueryWrapper<TransportPlanCv>()
                                    .eq("batch_number", cellValue)
                                    .eq("is_deleted", BasicDoc.NOT_DELETE));
                            if (transportPlanCv == null) {
                                transportPlanCv = new TransportPlanCv();
                                transportPlanCv.setId(RandomHelper.uuid());
                                transportPlanCv.setAddOrUpdate(0);
                                isAddPlan = true;
                            } else {
                                transportPlanCv.setAddOrUpdate(1);
                            }
                            planIdList.add(transportPlanCv.getId());
                        }
                        transportPlanCv.setBatchNumber(cellValue);
                        transportChildCv.setBatchNumber(cellValue);
                        break;
                    case 1: //运单号
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行运单号不能为空");
                            return msg;
                        }
                        transportChildCv.setWaybillNo(cellValue);
                        break;
                    case 2: //车架号
                        String frameNumber = cellValue;

                        if (StringUtils.isBlank(cellValue)) {
                            //车架号只能17位
                            if (frameNumber.getBytes().length != 17) {
                                msg.fail("第"+(r + 1) +"行车架号不为17位");
                                return msg;
                            }
                            //判断是否资源车
                            //生成虚拟车架号XNCJH+时间戳
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyMMdd");
//                            if (number) {
//                                //查询最后一辆虚拟车数据
//                                TransportChildCv tranChildCv = transportChildCvMapper.selectOne(new QueryWrapper<TransportChildCv>()
//                                        .eq("virtual_identity", 1)
//                                        .eq("is_deleted", BasicDoc.NOT_DELETE)
//                                        .orderByDesc("DATE_FORMAT(create_time,'%Y%m%d')")
//                                        .orderByDesc("frame_number")
//                                        .last("limit 1"));
//                                if (tranChildCv != null) {//判断日期
//                                    try {
//                                        Date dateOne = sdf.parse(tranChildCv.getFrameNumber().substring(2, 10));//车架号日期
//                                        Date dateTwo = sdf.parse(sdf.format(new Date()));//当前日期
//                                        if (dateOne.getTime() < dateTwo.getTime()) {//小于当前日期
//                                            resourcesNumber = 1000000;
//                                        } else if (dateOne.getTime() == dateTwo.getTime()) {//等于当天日期
//                                            resourcesNumber = Integer.parseInt(tranChildCv.getFrameNumber().substring(10, tranChildCv.getFrameNumber().length()));
//                                        }
//                                    } catch (Exception e) {
//                                        e.printStackTrace();
//                                        msg.fail("系统错误！");
//                                        return msg;
//                                    }
//                                }
//                                number = false;
//                            }
//                            resourcesNumber = resourcesNumber + 1;
//                            frameNumber = "XN" + sdf.format(new Date()) + resourcesNumber;
//                            transportChildCv.setVirtualIdentity(1);//表示资源车，虚拟车架号
                        }

//                        if (StringUtils.isEmpty(cellValue)) {
//                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                            msg.fail("第" + (r+1) + "行车架号不能为空");
//                            return msg;
//                        }
                        //验证车架号是否存在
                        int num = transportChildCvMapper.selectCount(new QueryWrapper<TransportChildCv>()
                                .eq("frame_number", frameNumber)
                                //.eq("waybill_no",transportChildCv.getWaybillNo())
                                .ne("state", 3)
                                .eq("is_deleted", BasicDoc.NOT_DELETE)
                        ).intValue();//为null说明是完整计划 不能重复
                        if (num > 0) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行车架号已存在");
                            return msg;
                        }
                        transportChildCv.setFrameNumber(frameNumber);
                        break;
                    case 3: //委托商
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行委托商不能为空");
                            return msg;
                        }
                        //验证同一批次号，委托商必须相同
                        if (!isAddPlan) {
                            if (!transportPlanCv.getConsignorName().equals(cellValue)) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行委托商与运输计划数据不一致");
                                return msg;
                            }
                        } else {
                            CustomerInfo customer = customerMap.get(cellValue);
                            if (customer == null) {
                                customer = customerInfoMapper.selectOne(new QueryWrapper<CustomerInfo>()
                                        .eq("customer_name", cellValue)
                                        .eq("is_delete", BasicDoc.NOT_DELETE));
                                if (customer == null) {
                                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                    msg.fail("第" + (r + 1) + "行委托商不存在");
                                    return msg;
                                }
                                if (customer.getStatus() != 1) {
                                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                    msg.fail("第" + (r + 1) + "行委托商状态必须是合作中");
                                    return msg;
                                }
                                customerMap.put(cellValue, customer);
                            }
                            transportPlanCv.setConsignorId(customer.getId());
                            transportPlanCv.setConsignorName(customer.getCustomerName());
                        }
                        transportChildCv.setConsignorId(transportPlanCv.getConsignorId());
                        transportChildCv.setConsignorName(cellValue);
                        break;
                    case 4: //运输方式
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行运输方式不能为空");
                            return msg;
                        }
                        int transportMode = 0;
                        switch (cellValue) {
                            case "公路":
                                transportMode = 1;
                                break;
                            case "水路":
                                transportMode = 2;
                                break;
                            case "水陆联运":
                                transportMode = 3;
                                break;
                            default:
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行运输方式格式错误");
                                return msg;
                        }
                        if (!isAddPlan && transportPlanCv.getTransportMode() != null) {
                            if (transportPlanCv.getTransportMode() != transportMode) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行运输方式与运输计划数据不一致");
                                return msg;
                            }
                        }
                        transportPlanCv.setTransportMode(transportMode);
                        break;
                    case 5: //提车场点
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行提车场点不能为空");
                            return msg;
                        }
                        transportChildCv.setPickCar(cellValue);
                        break;
                    case 6: //起运地
                        transportChildCv.setDeparturePlace(cellValue);
                        break;
                    case 7: //起运港
                        transportChildCv.setDeparturePort(cellValue);
                        break;
                    case 8: //目的港
                        transportChildCv.setArrivePort(cellValue);
                        break;
                    case 9: //目的地
                        transportChildCv.setArrivePlace(cellValue);
                        break;
//                    case 10:    //收车单位
//                        String[] transRulesTypes = transportPlanCv.getTransRulesType().split("-");
//                        if (transRulesTypes[1].equals("DO")) {
//                            if (StringUtils.isEmpty(cellValue)) {
//                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                                msg.fail("第" + (r + 1) + "行收车单位不能为空");
//                                return msg;
//                            }
//                        }
//                        transportChildCv.setDistributor(cellValue);
//                        break;
                    case 10:    //收车地址
//                        String[] transRulesTypess = transportPlanCv.getTransRulesType().split("-");
//                        if (transRulesTypess[1].equals("DO")) {
//                            if (StringUtils.isEmpty(cellValue)) {
//                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                                msg.fail("第" + (r + 1) + "行收车地址不能为空");
//                                return msg;
//                            }
//                        }
                        transportChildCv.setCollectCar(cellValue);
                        break;
                    case 11:    //品牌
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行品牌不能为空");
                            return msg;
                        }
                        CustomerCarBrand customerCarBrand = brandMap.get(cellValue);
                        if (customerCarBrand == null) {
                            customerCarBrand = customerCarBrandMapper.selectOne(new QueryWrapper<CustomerCarBrand>()
                                    .eq("brand", cellValue)
                                    .eq("is_deleted", BasicDoc.NOT_DELETE));
                            if (customerCarBrand == null) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行品牌不存在");
                                return msg;
                            }
                            if (customerCarBrand.getStatus() != BasicDoc.IS_USE) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行品牌禁用中");
                                return msg;
                            }
                            brandMap.put(cellValue, customerCarBrand);
                        }
                        transportChildCv.setBrandId(customerCarBrand.getId());
                        transportChildCv.setBrandName(customerCarBrand.getBrand());
                        break;
                    case 12: //型号
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行型号不能为空");
                            return msg;
                        }
                        CustomerCarModel customerCarModel = modelMap.get(transportChildCv.getBrandId() + cellValue);
                        if (customerCarModel == null) {
                            System.out.println(cellValue);
                            customerCarModel = customerCarModelMapper.selectOne(new QueryWrapper<CustomerCarModel>()
                                    .eq("brand_id", transportChildCv.getBrandId())
                                    .eq("model", cellValue)
                                    .eq("is_deleted", BasicDoc.NOT_DELETE));
                            if (customerCarModel == null) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行型号不存在");
                                return msg;
                            }
                            if (customerCarModel.getStatus() != BasicDoc.IS_USE) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行型号禁用中");
                                return msg;
                            }
                            modelMap.put(transportChildCv.getBrandId() + cellValue, customerCarModel);
                        }
                        transportChildCv.setModelId(customerCarModel.getId());
                        transportChildCv.setModelName(customerCarModel.getModel());
                        transportChildCv.setConfig(customerCarModel.getConfig());
                        transportChildCv.setWeight(customerCarModel.getWeight());
                        transportChildCv.setLwh(customerCarModel.getLength() + "x" + customerCarModel.getWidth() + "x" + customerCarModel.getHeight());
                        transportChildCv.setIsNewEnergy(customerCarModel.getIsNewEnergy());//区分新能源
                        break;
                    case 13:     //颜色
                        if (StringUtils.isNotBlank(cellValue)) {
                            transportChildCv.setColor(cellValue);
                        }
                        break;
                    case 14:    //计划时间
                        Date planTime = null;
                        try {
                            planTime = cell.getDateCellValue();
                        } catch (Exception e) {
                            try {
                                cellValue = getCellValue(cell);
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                                planTime = formatter.parse(cellValue);
                            } catch (Exception e1) {
                                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                                msg.fail("第" + (r + 1) + "行计划时间格式错误");
                                return msg;
                            }
                        }
                        if (planTime == null) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行计划时间不能为空");
                            return msg;
                        }
                        //transportPlanCv.setPlanTime(planTime);
                        transportChildCv.setPlanDate(planTime);
                        break;
                    case 15:    //起始地类型
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "起始地类型不能为空");
                            return msg;
                        }
                        if (StringUtils.isNotBlank(cellValue)) {
                            if (cellValue.equals("区域")) {
                                transportChildCv.setDeparturePlaceType(1);
                            }else if (cellValue.equals("港口")) {
                                transportChildCv.setDeparturePlaceType(2);
                            }
                        }
                        break;
                    case 16:    //交车地类型
                        if (StringUtils.isEmpty(cellValue)) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "交车地类型不能为空");
                            return msg;
                        }
                        if (StringUtils.isNotBlank(cellValue)) {
                            if (cellValue.equals("区域")) {
                                transportChildCv.setArrivePlaceType(1);
                            }else if (cellValue.equals("港口")) {
                                transportChildCv.setArrivePlaceType(2);
                            }
                        }
                        break;
                    case 17:    //备注
                        break;
                }
            }
            TransportChildCv transportChildOne = transportChildCvMapper.selectOne(new QueryWrapper<TransportChildCv>()
                    .eq("waybill_no", transportChildCv.getWaybillNo())
                    .eq("frame_number", transportChildCv.getFrameNumber())
                    .eq("is_deleted", BasicDoc.NOT_DELETE));
            if (transportChildOne != null) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                msg.fail("车架号为:" + transportChildOne.getFrameNumber() + "运单号已存在，请重新填写运单号，例：" + transportChildOne.getWaybillNo() + "_1！");
                return msg;
            }
            String key=transportChildCv.getWaybillNo()+transportChildCv.getFrameNumber();
            if(repeatMap.get(key)==null){
                repeatMap.put(key,transportChildCv);
            }else {
                msg.fail("车架号为:" + repeatMap.get(key).getFrameNumber() + "运单号已存在，请重新填写运单号，例：" +  repeatMap.get(key).getWaybillNo() + "_1！");
                return msg;
            }

            //VDC（主机厂库房）
            //GK（港口）
            //VSC（到中专库、区域）
            //DO（到经销商门店）
//            String[] transRulesType = transportPlanCv.getTransRulesType().split("-");
            SupplierPort startPort = null;
            SupplierPort endPort = null;
            LineConfig lineConfig1 = null;      //第一节线路
            LineConfig lineConfig2 = null;      //第二节线路
            LineConfig lineConfig3 = null;      //第三节线路
            //-----------验证第一节线路-----------
            if (transportPlanCv.getTransportMode() == 3 && transportChildCv.getDeparturePlaceType() ==1) {
                //============验证起始地============
                if (!StringUtils.isNotBlank(transportChildCv.getDeparturePlace())) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    msg.fail("第" + (r + 1) + "行起运地不能为空");
                    return msg;
                }
                String[] departurePlace = transportChildCv.getDeparturePlace().split("/");
                if (departurePlace.length != 2) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    msg.fail("第" + (r + 1) + "行起运地格式不正确(市/区)");
                    return msg;
                }
                //============验证起始港口============
                String departurePort = transportChildCv.getDeparturePort();
                if (!StringUtils.isNotBlank(departurePort)) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    msg.fail("第" + (r + 1) + "行起运港不能为空");
                    return msg;
                }
                startPort = portMap.get(departurePort);
                if (startPort == null) {
                    startPort = supplierPortOtherMapper.selectOne(new QueryWrapper<SupplierPort>()
                            .eq("port_name", departurePort)
                            .eq("is_deleted", BasicDoc.NOT_DELETE));
                    if (startPort == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        msg.fail("第" + (r + 1) + "行起运港口不存在");
                        return msg;
                    }
                    portMap.put(departurePort, startPort);
                }
                //============查询线路是否存在============
                QueryWrapper<LineConfig> query = new QueryWrapper<>();
                query.isNull("departure_port_id");
                query.eq("arrive_port_id", startPort.getId());
                query.eq("line_type", 1);
                query.eq("is_deleted", BasicDoc.NOT_DELETE);
                //验证是否为直辖市
                int num = BasicDoc.municipality_directly.indexOf(departurePlace[0]);
                if (num > 0) {
                    query.apply("start_address_name" + " like {0}", departurePlace[0] + "/%");
                    query.apply("start_address_name" + " like {0}", "%/" + departurePlace[1]);
                } else {
                    query.apply("start_address_name" + " like {0}", "%/" + departurePlace[0] + "/" + departurePlace[1]);
                }
                lineConfig1 = lineConfigMapper.selectOne(query);
                if (lineConfig1 == null) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    msg.fail("第" + (r + 1) + "行起运地到起运港线路不存在");
                    return msg;
                }
                transportChildCv.setDeparturePlaceType(1);
                transportChildCv.setDeparturePlaceId(lineConfig1.getStartAddressId());
                transportChildCv.setDeparturePlaceFullId(lineConfig1.getStartFullAddressId());
                transportChildCv.setDeparturePlace(lineConfig1.getStartAddressName());
                /*transportChildCv.setDeparturePortId(lineConfig1.getArrivePortId());
                transportChildCv.setDeparturePort(lineConfig1.getArrivePort());
                transportChildCv.setDeparturePortAreaId(lineConfig1.getEndAddressId());
                transportChildCv.setDeparturePortArea(lineConfig1.getEndAddressName());*/
                transportChildCv.setLine1Id(lineConfig1.getId());
            }
            //-----------验证第二节线路-----------
            if (transportPlanCv.getTransportMode() == 2 || transportPlanCv.getTransportMode() == 3) {
                //============验证起始港是否存在============
                if (startPort == null) {
                    String departurePort = transportChildCv.getDeparturePort();
                    if (!StringUtils.isNotBlank(departurePort)) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        msg.fail("第" + (r + 1) + "行起始港不能为空");
                        return msg;
                    }
                    startPort = portMap.get(departurePort);
                    if (startPort == null) {
                        startPort = supplierPortOtherMapper.selectOne(new QueryWrapper<SupplierPort>()
                                .eq("port_name", departurePort)
                                .eq("is_deleted", BasicDoc.NOT_DELETE));
                        if (startPort == null) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行起始港口不存在");
                            return msg;
                        }
                        portMap.put(departurePort, startPort);
                    }
                }
                //============验证交车港是否存在============
                String arrivePort = transportChildCv.getArrivePort();
                endPort = portMap.get(arrivePort);
                if (endPort == null) {
                    endPort = supplierPortOtherMapper.selectOne(new QueryWrapper<SupplierPort>()
                            .eq("port_name", arrivePort)
                            .eq("is_deleted", BasicDoc.NOT_DELETE));
                    if (endPort == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        msg.fail("第" + (r + 1) + "行交车港口不存在");
                        return msg;
                    }
                    portMap.put(arrivePort, endPort);
                }
                //============验证线路是否存在============
                lineConfig2 = lineConfigMapper.selectOne(new QueryWrapper<LineConfig>()
                        .eq("departure_port_id", startPort.getId())
                        .eq("arrive_port_id", endPort.getId())
                        .eq("line_type", 2)
                        .eq("is_deleted", BasicDoc.NOT_DELETE));
                if (lineConfig2 == null) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    msg.fail("第" + (r + 1) + "行起运港到交车港线路不存在");
                    return msg;
                }
//                if (transRulesType[0].equals("GK") || transportPlanCv.getTransportMode() == 2) {
//                    transportChildCv.setDeparturePlaceType(2);
//                    /*transportChildCv.setDeparturePlaceId(lineConfig2.getStartAddressId());
//                    transportChildCv.setDeparturePlace(lineConfig2.getStartAddressName());*/
//                }
                //起运港赋值
                transportChildCv.setDeparturePortId(lineConfig2.getDeparturePortId());
                transportChildCv.setDeparturePort(lineConfig2.getDeparturePort());
                transportChildCv.setDeparturePortAreaId(lineConfig2.getStartAddressId());
                transportChildCv.setDeparturePortArea(lineConfig2.getStartAddressName());
                //交车港赋值
//                if (transRulesType[1].equals("GK") || transportPlanCv.getTransportMode() == 2) {
//                    transportChildCv.setArrivePlaceType(2);
//                    /*transportChildCv.setArrivePlaceId(lineConfig2.getEndAddressId());
//                    transportChildCv.setArrivePlace(lineConfig2.getEndAddressName());*/
//                }
                transportChildCv.setArrivePortId(lineConfig2.getArrivePortId());
                transportChildCv.setArrivePort(lineConfig2.getArrivePort());
                transportChildCv.setArrivePortAreaId(lineConfig2.getEndAddressId());
                transportChildCv.setArrivePortArea(lineConfig2.getEndAddressName());
                transportChildCv.setLine2Id(lineConfig2.getId());
            }
            //-----------验证第三节线路-----------
            String startStr = "";   //返回到页面说明（开始阶段）
            String endStr = "";     //返回到页面说明（交车阶段）
            QueryWrapper<LineConfig> query = new QueryWrapper<>();
            if (transportPlanCv.getTransportMode() != 2) {
                //先验证公路
                if (transportPlanCv.getTransportMode() == 1) {
                    //=========验证起运地=========
                    if (transportChildCv.getDeparturePlaceType()==1) {
                        if (!StringUtils.isNotBlank(transportChildCv.getDeparturePlace())) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行起运地不能为空");
                            return msg;
                        }
                        String[] departurePlace = transportChildCv.getDeparturePlace().split("/");
                        if (departurePlace.length != 2) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行起运地格式错误(市/区)");
                            return msg;
                        }
                        int num = BasicDoc.municipality_directly.indexOf(departurePlace[0]);
                        if (num > 0) {
                            query.apply("start_address_name" + " like {0}", departurePlace[0] + "/%");
                            query.apply("start_address_name" + " like {0}", "%/" + departurePlace[1]);
                        } else {
                            query.apply("start_address_name" + " like {0}", "%/" + departurePlace[0] + "/" + departurePlace[1]);
                        }
                        query.isNull("departure_port_id");
                        transportChildCv.setDeparturePlaceType(1);
                        startStr = "起运地";
                    } else if (transportChildCv.getDeparturePlaceType()==2) {
                        if (!StringUtils.isNotBlank(transportChildCv.getDeparturePort())) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行起运港不能为空");
                            return msg;
                        }
                        query.eq("departure_port", transportChildCv.getDeparturePort());
                        transportChildCv.setDeparturePlaceType(2);
                        startStr = "起运港";
                    } else {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        msg.fail("第" + (r + 1) + "行运输条款起运格式错误");
                        return msg;
                    }
                    //=========验证交车地=========
                    if (  transportChildCv.getArrivePlaceType()==1) {
                        if (!StringUtils.isNotBlank(transportChildCv.getArrivePlace())) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行目的地不能为空");
                            return msg;
                        }
                        String[] arrivePlace = transportChildCv.getArrivePlace().split("/");
                        if (arrivePlace.length != 2) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行目的地格式不正确(市/区)");
                            return msg;
                        }
                        int num = BasicDoc.municipality_directly.indexOf(arrivePlace[0]);
                        if (num > 0) {
                            query.apply("end_address_name" + " like {0}", arrivePlace[0] + "/%");
                            query.apply("end_address_name" + " like {0}", "%/" + arrivePlace[1]);
                        } else {
                            query.apply("end_address_name" + " like {0}", "%/" + arrivePlace[0] + "/" + arrivePlace[1]);
                        }
                        query.isNull("arrive_port_id");
                        transportChildCv.setArrivePlaceType(1);
                        endStr = "目的地";
                    } else if ( transportChildCv.getArrivePlaceType()==2) {
                        if (!StringUtils.isNotBlank(transportChildCv.getArrivePort())) {
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                            msg.fail("第" + (r + 1) + "行目的港口不能为空");
                            return msg;
                        }
                        query.eq("arrive_port", transportChildCv.getArrivePort());
                        transportChildCv.setArrivePlaceType(2);
                        endStr = "目的港";
                    }
                    query.eq("line_type", 1);
                    query.eq("is_deleted", BasicDoc.NOT_DELETE);
                    lineConfig3 = lineConfigMapper.selectOne(query);
                    if (lineConfig3 == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        msg.fail("第" + (r + 1) + "行" + startStr + "到" + endStr + "线路不存在");
                        return msg;
                    }
                    transportChildCv.setLine3Id(lineConfig3.getId());
                    if (transportChildCv.getDeparturePlaceType() == 2) {
                        transportChildCv.setDeparturePortId(lineConfig3.getDeparturePortId());
                        transportChildCv.setDeparturePort(lineConfig3.getDeparturePort());
                        transportChildCv.setDeparturePortAreaId(lineConfig3.getStartAddressId());
                        transportChildCv.setDeparturePortArea(lineConfig3.getStartAddressName());
                    } else {
                        transportChildCv.setDeparturePlaceId(lineConfig3.getStartAddressId());   //起运地区域id
                        transportChildCv.setDeparturePlaceFullId(lineConfig3.getStartFullAddressId());
                        transportChildCv.setDeparturePlace(lineConfig3.getStartAddressName());   //起运地区域名
                    }
                    if (transportChildCv.getArrivePlaceType() == 2) {
                        transportChildCv.setArrivePortId(lineConfig3.getArrivePortId());
                        transportChildCv.setArrivePort(lineConfig3.getArrivePort());
                        transportChildCv.setArrivePortAreaId(lineConfig3.getEndAddressId());
                        transportChildCv.setArrivePortArea(lineConfig3.getEndAddressName());
                    } else {
                        transportChildCv.setArrivePlaceId(lineConfig3.getEndAddressId());        //到达地区域id
                        transportChildCv.setArrivePlaceFullId(lineConfig3.getEndFullAddressId());
                        transportChildCv.setArrivePlace(lineConfig3.getEndAddressName());        //到达地区域名
                    }
                } else if (transportPlanCv.getTransportMode() == 3 && transportChildCv.getArrivePlaceType()!=2) {

                        query.isNull("arrive_port_id");
                        endStr = "目的地";
                    query.eq("line_type", 1);
                    query.eq("departure_port_id", transportChildCv.getArrivePortId());
                    query.eq("is_deleted", BasicDoc.NOT_DELETE);
                    lineConfig3 = lineConfigMapper.selectOne(query);
                    if (lineConfig3 == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        msg.fail("第" + (r + 1) + "行港口到" + endStr + "线路不存在");
                        return msg;
                    }
                    transportChildCv.setArrivePlaceType(1);
                    transportChildCv.setArrivePlaceId(lineConfig3.getEndAddressId());
                    transportChildCv.setArrivePlaceFullId(lineConfig3.getEndFullAddressId());
                    transportChildCv.setArrivePlace(lineConfig3.getEndAddressName());
                    transportChildCv.setLine3Id(lineConfig3.getId());
                }
            }
            if (transportPlanCv.getTransportMode() == 3) {
                //验证水路联运至少有两节线路
                int num = 0;
                if (transportChildCv.getLine1Id() != null) {
                    num++;
                }
                if (transportChildCv.getLine2Id() != null) {
                    num++;
                }
                if (transportChildCv.getLine3Id() != null) {
                    num++;
                }
                if (num <= 1) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    msg.fail("第" + (r + 1) + "行运输条款格式不正确");
                    return msg;
                }
            }
            //赋值当前位置
            if (lineConfig1 != null) {
                transportChildCv.setActualPositionId(lineConfig1.getStartAddressId());
                transportChildCv.setActualPositionFullId(lineConfig1.getStartFullAddressId());
                transportChildCv.setActualPositionName(lineConfig1.getStartAddressName());
                transportChildCv.setActualPositionCoordinate(lineConfig1.getStartingCoordinate());
            } else if (lineConfig1 == null && lineConfig2 != null) {
                transportChildCv.setActualPositionId(lineConfig2.getStartAddressId());
                transportChildCv.setActualPositionFullId(lineConfig2.getStartFullAddressId());
                transportChildCv.setActualPositionName(lineConfig2.getStartAddressName());
                transportChildCv.setActualPositionCoordinate(lineConfig2.getStartingCoordinate());
                transportChildCv.setActualPortId(lineConfig2.getDeparturePortId());
                transportChildCv.setActualPortName(lineConfig2.getDeparturePort());
            } else {
                transportChildCv.setActualPositionId(lineConfig3.getStartAddressId());
                transportChildCv.setActualPositionFullId(lineConfig3.getStartFullAddressId());
                transportChildCv.setActualPositionName(lineConfig3.getStartAddressName());
                transportChildCv.setActualPositionCoordinate(lineConfig3.getStartingCoordinate());
                transportChildCv.setActualPortId(lineConfig3.getDeparturePortId());
                transportChildCv.setActualPortName(lineConfig3.getDeparturePort());
            }
            if (isAddPlan) {
                transportPlanCv.setPlanNumber(getTransportPlanCode());
                transportPlanCv.setPlanCount(1);    //委托车数量
                transportPlanCv.setLeftoverPlanCount(1);    //剩余委托车数量
                transportPlanCv.setState(0);        //状态 0待审核 1执行中 2已完成 3部分完成 4 待提交审核  -1已取消
                transportPlanCv.setIsDeleted(BasicDoc.NOT_DELETE);
                transportPlanCv.setCreateTime(new Date());
                transportPlanMap.put(transportPlanCv.getBatchNumber(), transportPlanCv);
            } else {
                transportPlanCv.setPlanCount(transportPlanCv.getPlanCount() + 1);    //委托车数量
                transportPlanCv.setLeftoverPlanCount(transportPlanCv.getLeftoverPlanCount() + 1);    //剩余委托车数量
                transportPlanMap.put(transportPlanCv.getBatchNumber(), transportPlanCv);
            }
            transportChildCv.setId(RandomHelper.uuid());
            transportChildCv.setBasicId(transportPlanCv.getId());
            transportChildCv.setIsOutStorage(0);        //是否出库 0：未出库 1：已出库
            transportChildCv.setState(0);               //状态 0：待分配运输 1：已分配运输 2：运输中 3：已交车
            transportChildCv.setIsDeleted(BasicDoc.NOT_DELETE);
            transportChildCv.setCreateTime(date);

            transportChildCvList.add(transportChildCv);
            perfectLiFramenos.add(transportChildCv.getFrameNumber());
//            depaturePortFramenos.put(transportChildCv.getFrameNumber(),transportChildCv.getDeparturePort());
            countNum++;
        }
        if (transportChildCvList != null && transportChildCvList.size() > 0) {
            transportChildCvMapper.batchChildCvList(transportChildCvList);//批量插入ChildCv提高速度
        }
        int num1 = 0;
        int num2 = 0;
        for (String dataKey : transportPlanMap.keySet()) {
            TransportPlanCv transportPlanCv = transportPlanMap.get(dataKey);
            if (transportPlanCv.getAddOrUpdate() == 0) {
                transportPlanCvMapper.insert(transportPlanCv);
                num1++;
            } else {
                transportPlanCvMapper.updateById(transportPlanCv);
                num2++;
            }
        }
        for (String id : planIdList) {
            //添加操作日志
            OperateRecord ope = new OperateRecord();
            ope.setId(RandomHelper.uuid());
            ope.setOperateType("批量导入计划");

            ope.setSourceId(id);
            if (empVo !=null){
                ope.setOperationId(empVo.getId());
                ope.setOperationName(empVo.getEmpNm());
            }
            operateRecordMapper.insert(ope);
        }
        msg.success("成功新增" + num1 + "条运输计划,修改" + num2 + "条运输计划," + countNum + "条计划委托车");
        return msg;
    }

    public String getCellValue(Cell cell) throws Exception {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) { // 处理日期格式
                    cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue().trim();
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                switch (cell.getCachedFormulaResultType()) { // 获取公式结果类型
                    case NUMERIC:
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        break;
                    case STRING:
                        cellValue = cell.getStringCellValue().trim();
                        break;
                    default:
                        cellValue = "公式结果类型未处理";
                }
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 错误
                cellValue = "错误单元格";
                break;
            default:
                cellValue = "未知类型 (" + cell.getCellType() + ")";
                break;
        }
        return cellValue.trim();
    }

    /**
     * 获取运输计划编号
     * @return
     */
    public static String getTransportPlanCode(){
        return "YSJH"+getDateTime()+getRandom();
    }
    /**
     * 生成时间戳
     */
    private static String getDateTime() {
        DateFormat sdf = new SimpleDateFormat("yyMMddHHmmss");
        return sdf.format(new Date());
    }

    /**
     * 随机数
     */
    public static String getRandom() {
        int rs = (int) ((Math.random() * 999 + 1) * Math.pow(10, 1 - 1));
        return String.valueOf(rs);
    }


    @Override
    public ResponseMsg<IPage<TransportChildCv>> wtcPageList(TransportChildCv transportChildCv, EmployeeVo empVo) {
        if (StringUtils.isNotBlank(transportChildCv.getBatchNumber())) {
            String[] batchNumberLis = transportChildCv.getBatchNumber().split(",");
            if (batchNumberLis.length > 0) {
                String batchNumber = transportChildCv.getBatchNumber().replace(",", "','");
                transportChildCv.setBatchNumber("'" + batchNumber + "'");
            }
        }
        if (StringUtils.isNotBlank(transportChildCv.getWaybillNo())) {
            String[] waybillNoLis = transportChildCv.getWaybillNo().split(",");
            if (waybillNoLis.length > 0) {
                String waybillNo = transportChildCv.getWaybillNo().replace(",", "','");
                transportChildCv.setWaybillNo("'" + waybillNo + "'");
            }
        }
        List<String> frameNumbers = Arrays.asList(transportChildCv.getFrameNumber().split(","));
        if (!CollectionUtils.isEmpty(frameNumbers) && frameNumbers.size() > 1) {
            transportChildCv.setFrameNumbers(frameNumbers);
            transportChildCv.setFrameNumber(null);
        }
        List<TransportChildCv> transportChildCvlist =transportChildCvMapper.wtcPageList(transportChildCv)   .stream()
                .distinct()  // 基于 equals/hashCode 去重
                .collect(Collectors.toList());;

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        List<String>childIdsList = transportChildCvlist.stream().map(TransportChildCv::getId).collect(Collectors.toList());
        List<VehicleDeployCarCv> vehicleDeployCarCvList = vehicleDeployCarCvMapper.selectList(null,
                new QueryWrapper<VehicleDeployCarCv>().in("car_id",childIdsList).eq("is_deleted",0));
            // 1. 按car_id分组VehicleDeployCar数据
        Map<String, List<VehicleDeployCarCv>> deployMap = vehicleDeployCarCvList.stream()
                .collect(Collectors.groupingBy(VehicleDeployCarCv::getCarId));
        transportChildCvlist.forEach(cv -> {
            // 2. 获取关联的运输段数据
            List<VehicleDeployCarCv> relatedDeployList = deployMap.getOrDefault(cv.getId(), Collections.emptyList());

            // 3. 拓扑排序（处理运输段连续性）
            List<VehicleDeployCarCv> sortedList = topologicalSort(relatedDeployList);

            // 4. 转换为ActualTransportation格式
            List<ActualTransportation> actualTransportations = sortedList.stream()
                    .map(this::convertToActualTransport)
                    .collect(Collectors.toList());

            cv.setObjectDate(actualTransportations);
        });

        ResponseMsg<IPage<TransportChildCv>> msg = new ResponseMsg<>();
        Page<TransportChildCv> page = new Page<>();
        page.setRecords(transportChildCvlist);
        page.setTotal(transportChildCvlist.size());
        page.setSize(transportChildCv.getRows());
        page.setCurrent(transportChildCv.getPage());
        msg.setData(page);
        msg.success("查询成功");
        return msg;
    }

    // 拓扑排序保证运输段连续性
    private List<VehicleDeployCarCv> topologicalSort(List<VehicleDeployCarCv> list) {
        List<VehicleDeployCarCv> sorted = new ArrayList<>();
        Set<VehicleDeployCarCv> visited = new HashSet<>();

        // 获取所有起点节点（无前驱的节点）
        List<VehicleDeployCarCv> startNodes = list.stream()
                .filter(node -> list.stream().noneMatch(n -> isPredecessor(n, node)))
                .collect(Collectors.toList());

        // 按装车时间升序排序起点
        startNodes.sort(Comparator.comparing(v -> v.getLoadingDate()));

        // 递归访问每个起点节点
        startNodes.forEach(node -> visit(node, list, sorted, visited));

        return sorted;
    }
    // 递归访问节点
    private void visit(VehicleDeployCarCv node,
                       List<VehicleDeployCarCv> all,
                       List<VehicleDeployCarCv> sorted,
                       Set<VehicleDeployCarCv> visited) {
        if (!visited.contains(node)) {
            visited.add(node);
            // 找到所有后继节点
            all.stream()
                    .filter(n -> isPredecessor(node, n))
                    .forEach(n -> visit(n, all, sorted, visited));
            sorted.add(node);
        }
    }

    // 判断前驱关系
    private boolean isPredecessor(VehicleDeployCarCv prev, VehicleDeployCarCv next) {
        String prevDelivery = safeString(prev.getDeliveryName()) + "-" + safeString(prev.getDeliveryPortName());
        String nextStart = safeString(next.getStartName()) + "-" + safeString(next.getStartPortName());
        return prevDelivery.equals(nextStart);
    }

    // 安全获取字符串
    private String safeString(String str) {
        return str == null ? "" : str;
    }

    // 对象转换方法
    private ActualTransportation convertToActualTransport(VehicleDeployCarCv v) {
        ActualTransportation actual = new ActualTransportation();
        actual.setTransport(v.getPersonnelName());
        actual.setTransportType(v.getType() == 0 ? "公路" : "水路");
        actual.setLoadingDate(v.getLoadingDate());
        actual.setDeliveryDate(v.getDeliveryDate());
        actual.setSupplierName(v.getSupplierName());

        // 处理可能为null的字段
        actual.setLoadingPlace(safeString(v.getStartName()) + "-" + safeString(v.getStartPortName()));
        actual.setDeliveryPlace(safeString(v.getDeliveryName()) + "-" + safeString(v.getDeliveryPortName()));
        actual.setVoyageNumber(v.getVoyageNumber());

        return actual;
    }
    /**
     * 供应商统计数据分析
     * @param cv
     * @return
     */
    @Override
    public ResponseMsg<List<SupplierStatsDTO>> getSupplierStats(VehicleDeployCarCv cv) {
        ResponseMsg<List<SupplierStatsDTO>> msg = new ResponseMsg<>();

        // 构建查询条件
        QueryWrapper<VehicleDeployCarCv> wrapper = new QueryWrapper<>();
        wrapper.select("supplier_name AS supplierName", "COUNT(*) AS count")
                .groupBy("supplier_name")
                .eq("is_deleted", BasicDoc.NOT_DELETE)
                .eq(cv.getPartYearMonth() != null, "part_year_month", cv.getPartYearMonth())
                .orderByDesc("count")
                .isNotNull("supplier_name"); // 过滤空供应商

        try {
            // 执行查询并映射结果
            List<SupplierStatsDTO> stats = vehicleDeployCarCvMapper.selectMaps(wrapper)
                    .stream()
                    .map(map -> new SupplierStatsDTO(
                            (String) map.get("supplierName"),
                            ((Long) map.get("count")).intValue()
                    ))
                    .collect(Collectors.toList());

            if (stats.isEmpty()) {
                msg.fail("未找到供应商统计数据");
            } else {
                msg.setData(stats);
                msg.success("供应商统计查询成功");
            }
        } catch (Exception e) {
            msg.fail("统计查询失败：" + e.getMessage());
        }
        return msg;
    }

    /**
     * 客户统计数据分析
     * @param transportChildCv
     * @return
     */
    @Override
    public ResponseMsg<List<ConsignorStatsDTO>> getConsignorStats(TransportChildCv transportChildCv) {
        ResponseMsg<List<ConsignorStatsDTO>> msg = new ResponseMsg();
        QueryWrapper<TransportChildCv> wrapper = new QueryWrapper<>();
        wrapper.select("consignor_name AS consignorName", "COUNT(*) AS count")
                .groupBy("consignor_name")
                .eq("is_deleted", BasicDoc.NOT_DELETE)
                .eq("part_year_month",transportChildCv.getPartYearMonth())
                .orderByDesc("count")
                .isNotNull("consignor_name");  // 增加空值过滤
        List<ConsignorStatsDTO> stats = transportChildCvMapper.selectMaps(wrapper)
                .stream()
                .map(map -> new ConsignorStatsDTO(
                        (String) map.get("consignorName"),
                        ((Long) map.get("count")).intValue()
                ))
                .collect(Collectors.toList());
        if (stats.isEmpty()) {
            msg.fail("未找到相关统计数据");
        } else {
            msg.setData(stats);
            msg.success("统计查询成功");
        }
        return msg;
    }

    /**
     * 计划和实际运输数据分析
     * @param transportChildCv
     * @return
     */
    @Override
    public ResponseMsg<List<VehicleStatistics>> getVehicleStats(TransportChildCv transportChildCv) {
        Integer year = transportChildCv.getPartYear();
        // 1. 查询计划运量
        List<VehicleStatistics> planStats = transportChildCvMapper.selectPlanStats(year);

        // 2. 查询实际运量
        List<VehicleStatistics> actualStats = vehicleDeployCarCvMapper.selectActualStats(year);

        // 3. 合并数据
        Map<String, VehicleStatistics> resultMap = new LinkedHashMap<>();
        IntStream.rangeClosed(1, 12).forEach(month -> {
            // 格式化月份为两位数字符串（1→"01", 10→"10"）
            String formattedMonth = String.format("%02d", month);
            // 组合年份和月份（如：202501）
            String yearMonth = year.toString() + formattedMonth;
            resultMap.put(yearMonth, new VehicleStatistics(
                    yearMonth,  // 使用组合后的年月字符串
                    0,
                    0
            ));
//            resultMap.put(month, new VehicleStatistics(month, 0, 0));
        });

//        planStats.forEach(dto -> resultMap.get(dto.getMonth()).setPlanCount(dto.getPlanCount()));
        for ( VehicleStatistics dto : planStats){
            if (resultMap.get(dto.getMonth())!=null){
                resultMap.get(dto.getMonth()).setPlanCount(dto.getPlanCount());
            }
        }
//        actualStats.forEach(dto -> resultMap.get(dto.getMonth()).setActualCount(dto.getActualCount()));
        for ( VehicleStatistics dto : actualStats){
            if (resultMap.get(dto.getMonth())!=null){
                resultMap.get(dto.getMonth()).setActualCount(dto.getActualCount());
            }
        }
        ResponseMsg<List<VehicleStatistics>> msg = new ResponseMsg();
        msg.success("计划与实际运量统计查询成功");
        msg.setData(new ArrayList<>(resultMap.values()));
        return msg;
    }
}
